﻿using LY.Entity;
using LY.Entity.Entities;
using LY.Entity.PageSearch;
using LY.Entity.SearchEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LY.DataAccess.Repository
{
    public class ClaimsRepository : BaseRepository
    {
        //获取新手/非新手项目
        public List<T_Project_EX> GetProjects(int type, int top)
        {
            string sql = string.Format(@"SELECT t.* FROM (SELECT top {0}  A.[ID]
                ,A.[ProjectNo]
                ,A.[ProjectName]
                ,convert(varchar, cast(round(A.TotalLoan,2) as money),1) as TotalLoanStr
                ,CONVERT(DECIMAL(18,2),(A.[TotalLoan]/10000)) AS TotalLoan
                ,CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID and T_Investment.Status=0 )) as TenderAmt
                ,CONVERT(INT,(CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID and T_Investment.Status=0 ))
                /(CASE  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) WHEN 0 THEN 1 else  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) end))*100) AS Rates
                ,CONVERT(DECIMAL(18,1),A.[AnnualisedRates]*100) AS AnnualisedRatesStr
                ,CONVERT(DECIMAL(18,1),A.[AddAnnualisedRates]*100) AS AddAnnualisedRatesStr
                ,AnnualisedRates
                ,AddAnnualisedRates
                ,A.[EnterpriseID]
                ,A.[ProjectBeginDate]
                ,A.[ProjectEndDate]
                ,A.[LoanType]
                ,A.[AuditStatus]
                ,A.[LoanStatus]
                ,A.[RepaymentStatus]
                ,A.[TenderStatus]
                ,A.[TenderTime]
                ,A.[ProjectDuration]
                ,A.[DurationMonthOrDay],
                (select COUNT(MemberId) from T_Investment where ProjectId =A.ID) AS TendMembers       
                ,case 
				when DATEDIFF(S,ProjectBeginDate,GETDATE()) > 0 AND DATEDIFF(S,GETDATE(),ProjectEndDate)>0 AND AuditStatus=2  AND TenderStatus=0 
                then 2
                when A.RepaymentStatus = 0 AND  TenderStatus=1  AND Convert(varchar(10), A.TenderTime,23)=Convert(varchar(10), GETDATE(),23)
                then 3
                when A.RepaymentStatus = 0 AND  Convert(varchar(10), GETDATE(),23)> Convert(varchar(10), A.[ProjectEndDate],23) OR A.RepaymentStatus = 0 AND  TenderStatus=1 and Convert(varchar(10), GETDATE(),23)>Convert(varchar(10),A.TenderTime ,23) 
                then 4
                when A.RepaymentStatus = 1 
                then 5
                when DATEDIFF(S,ProjectBeginDate,GETDATE()) <0
                then 1  
                else 6 end as ProjectStatus 
                FROM [T_Project] A   WHERE A.[AuditStatus]=2 AND A.IsNewProject=@IsNewProject order by A.ProjectBeginDate DESC )t ORDER BY T.ProjectStatus ASC,t.ProjectBeginDate DESC", top);

            return base.Query<T_Project_EX>(sql, new { IsNewProject = type });
        }

        //通过id获取项目[返款状态]
        public T_Project_EX GetProjects(string id)
        {
            string sql = @"SELECT t.* FROM (SELECT A.[ID]
                ,A.[ProjectNo]
                ,A.[ProjectName]
                ,convert(varchar, cast(round(A.TotalLoan,2) as money),1) as TotalLoanStr
                ,CONVERT(DECIMAL(18,2),(A.[TotalLoan]/10000)) AS TotalLoan
                ,CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID and T_Investment.Status=0 )) as TenderAmt
                ,CONVERT(INT,(CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID and T_Investment.Status=0 ))
                /(CASE  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) WHEN 0 THEN 1 else  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) end))*100) AS Rates
               ,CONVERT(DECIMAL(18,1),A.[AnnualisedRates]*100) AS AnnualisedRates
                ,CONVERT(DECIMAL(18,1),A.[AddAnnualisedRates]*100) AS AddAnnualisedRates
                ,A.[EnterpriseID]
                ,A.[ProjectBeginDate]
                ,A.[ProjectEndDate]
                ,A.[LoanType]
                ,A.[AuditStatus]
                ,A.[LoanStatus]
                ,A.[RepaymentStatus]
                ,A.[TenderStatus]
                ,A.[TenderTime]
                ,A.[ProjectDuration]
                ,A.[DurationMonthOrDay],
                (select COUNT(MemberId) from T_Investment where ProjectId =A.ID) AS TendMembers       
                ,case 
				when DATEDIFF(S,ProjectBeginDate,GETDATE()) > 0 AND DATEDIFF(S,GETDATE(),ProjectEndDate)>0 AND AuditStatus=2  AND TenderStatus=0 
                then 2
                when A.RepaymentStatus = 0 AND  TenderStatus=1  AND Convert(varchar(10), A.TenderTime,23)=Convert(varchar(10), GETDATE(),23)
                then 3
                when A.RepaymentStatus = 0 AND  Convert(varchar(10), GETDATE(),23)> Convert(varchar(10), A.[ProjectEndDate],23) OR A.RepaymentStatus = 0 AND  TenderStatus=1 and Convert(varchar(10), GETDATE(),23)>Convert(varchar(10),A.TenderTime ,23) 
                then 4
                when A.RepaymentStatus = 1 
                then 5
                when DATEDIFF(S,ProjectBeginDate,GETDATE()) <0
                then 1  
                else 6 end as ProjectStatus 
                FROM [T_Project] A  WHERE A.[AuditStatus]=2 and A.[ID]=@ID)t";

            return base.Get<T_Project_EX>(sql, new { ID = id });
        }

        //获取所有投资项目[列表页]
        public PagedList<T_Project_EX> GetProjects(PageView view, InvestListSearch condition)
        {
            string cols = @"t.*";

            string sqlWhere = " and AuditStatus=2";
            //项目状态
            if (!string.IsNullOrEmpty(condition.Status))
            {
                sqlWhere += " and ProjectStatus=" + condition.Status;
            }
            //项目期限
            if (!string.IsNullOrEmpty(condition.Range))
            {
                string RangeBegin = "";
                string RangeEnd = "";
                if (condition.Range == "-1")
                {
                    sqlWhere += " and DurationMonthOrDay=2";
                }
                else
                {
                    RangeBegin = " and DurationMonthOrDay=1 and ProjectDuration<" + condition.Range.Split(',')[1];
                    RangeEnd = " and DurationMonthOrDay=1 and ProjectDuration>=" + condition.Range.Split(',')[0];

                    if (condition.Range.Split(',')[0] == "0")
                    {
                        sqlWhere += RangeBegin;
                    }
                    else if (condition.Range.Split(',')[1] == "0")
                    {
                        sqlWhere += RangeEnd;
                    }
                    else
                    {
                        sqlWhere += RangeEnd + RangeBegin;
                    }
                }
            }
            //项目收益
            if (!string.IsNullOrEmpty(condition.Rate))
            {
                string RateBegin = " and AnnualisedRates+AddAnnualisedRates<" + Convert.ToDouble(condition.Rate.Split(',')[1]);
                string RateEnd = " and AnnualisedRates+AddAnnualisedRates>=" + Convert.ToDouble(condition.Rate.Split(',')[0]);
                if (condition.Rate.Split(',')[0] == "0")
                {
                    sqlWhere += RateBegin;
                }
                else if (condition.Rate.Split(',')[1] == "0")
                {
                    sqlWhere += RateEnd;
                }
                else
                {
                    sqlWhere += RateEnd + RateBegin;
                }
            }
            //项目类型
            if (!string.IsNullOrEmpty(condition.Type))
            {
                sqlWhere += " and IsNewProject=" + condition.Type;
            }

            return base.PageGet<T_Project_EX>(view, cols, @"(SELECT A.[ID]
                ,A.[ProjectNo]
                ,A.[ProjectName]
                ,convert(varchar, cast(round(A.TotalLoan,2) as money),1) as TotalLoanStr
                ,CONVERT(DECIMAL(18,2),(A.[TotalLoan])) AS TotalLoan
                ,CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID and T_Investment.Status=0 )) as TenderAmt
                ,CONVERT(INT,(CONVERT(DECIMAL(18,2), (SELECT ISNULL(SUM(TenderAmt),0) FROM T_Investment WHERE ProjectId=A.ID and T_Investment.Status=0 ))
                /(CASE  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) WHEN 0 THEN 1 else  CONVERT(DECIMAL(18,2),(A.[TotalLoan])) end))*100) AS Rates
               ,CONVERT(DECIMAL(18,1),A.[AnnualisedRates]*100) AS AnnualisedRates
                ,CONVERT(DECIMAL(18,1),A.[AddAnnualisedRates]*100) AS AddAnnualisedRates
                ,A.[EnterpriseID]
                ,A.[ProjectBeginDate]
                ,A.[ProjectEndDate]
                ,A.[LoanType]
                ,A.[AuditStatus]
                ,A.[LoanStatus]
                ,A.[RepaymentStatus]
                ,A.[TenderStatus]
                ,A.[TenderTime]
                ,A.[ProjectDuration]
                ,A.[DurationMonthOrDay]
                ,A.[IsNewProject]
                ,(select COUNT(MemberId) from T_Investment where ProjectId =A.ID) AS TendMembers       
                ,case 
				when DATEDIFF(S,ProjectBeginDate,GETDATE()) > 0 AND DATEDIFF(S,GETDATE(),ProjectEndDate)>0 AND AuditStatus=2  AND TenderStatus=0 
                then 2
                when A.RepaymentStatus = 0 AND  TenderStatus=1  AND Convert(varchar(10), A.TenderTime,23)=Convert(varchar(10), GETDATE(),23)
                then 3
                when A.RepaymentStatus = 0 AND  Convert(varchar(10), GETDATE(),23)> Convert(varchar(10), A.[ProjectEndDate],23) OR A.RepaymentStatus = 0 AND  TenderStatus=1 and Convert(varchar(10), GETDATE(),23)>Convert(varchar(10),A.TenderTime ,23) 
                then 4
                when A.RepaymentStatus = 1 
                then 5
                when DATEDIFF(S,ProjectBeginDate,GETDATE()) <0
                then 1  
                else 6 end as ProjectStatus 
                FROM [T_Project] A  WHERE A.[AuditStatus]=2)t", sqlWhere, "[ProjectBeginDate] DESC", "");
        }

        //获取投资排行
        public List<T_Investment_EX> GetHotInvestors(int? top)
        {
            string sql = string.Format(@"select TT.Name,
                             TT.TenderAmt,
                              case 
                            when TT.TenderAmt<=10000 then 1 
                            when TT.TenderAmt>10000 and TT.TenderAmt<=100000 then 2
                            when TT.TenderAmt>100000 and TT.TenderAmt<500000 then 3
                            when TT.TenderAmt>500000 and TT.TenderAmt<1000000 then 4
                            when TT.TenderAmt>1000000 then 5 end TenderAmtLevel 
                              from
                             (
                             select top {0} T1.MemberId ,t2.Name, SUM(T1.TenderAmt) AS TenderAmt  from T_Investment AS T1 
                             left join U_Member T2 on t1.MemberId=t2.ID
                             group by T1.MemberId,t2.Name,T1.Status  having T1.Status=1 order by TenderAmt desc
                             ) AS TT ", top);
            return base.Query<T_Investment_EX>(sql, null);
        }

        //通过项目id获取项目详情
        public T_Project_EX GetProjectById(string id)
        {
            string sql = @"SELECT T_Project.[Id],[ProjectNo],
                            [ProjectName],
                            [ProjectContent],
                            [ProjectDetail],
                            [TotalLoan],
                            [AuditTime],
                            [AnnualisedRates],
                            [AddAnnualisedRates],
                            [EnterpriseID],
                            [ProjectLocation],
                            [ProjectDuration],
                            [TenderStatus],
                            [MinTenderAmount],
                            [DurationMonthOrDay],
                            [RepaymentType],
                            [ProjectBeginDate],
                            [LoanTime],
                            [ProjectEndDate],
                            [TenderTime],
                            [IsNewProject],
                            [CreateTime],
                            [VerifyIdCard],
                            [VerifyIsMarriage],
                            [VerifyBooklets],
                            [VerifyHouseProperty],
                            [VerifyBankWater],
                            [VerifyRealEstateBook],
                            [VerifyCreditReporter],
                            [VerifyOthers],
                            T_WindControlInformation.FKRemark,
                            case 
                            when [RepaymentType]=1 then '按月付息到期还本'
                            when [RepaymentType]=2 then '一次性还本付息'
                            when [RepaymentType]=3 then '等额本息'
                            end  [RepaymentTypeStr],
                            case 
                            when [DurationMonthOrDay]=1 then CONVERT(VARCHAR(20),[ProjectDuration])+'个月'
                            when [DurationMonthOrDay]=2 then CONVERT(VARCHAR(20),[ProjectDuration])+'天'
                            end [ProjectDurationStr],
                            case
                            when [DurationMonthOrDay]=1 then DATEADD(M,ProjectDuration,LoanTime)
                            when [DurationMonthOrDay]=2 then DATEADD(D,ProjectDuration,LoanTime)
                            end [ProjectEndStr]
                            FROM T_Project,dbo.T_WindControlInformation
                            WHERE T_Project.ID=T_WindControlInformation.ProjectID and T_Project.ID=@ID";

            return base.Get<T_Project_EX>(sql, new { ID = id });
        }

        //获取我的投资记录
        public PagedList<T_Project_EX> GetProjectOfMine(PageView view, string id, ClaimsSearch condition)
        {
            string cols = @"SubmitBidNo,
                            ID,
                            MemberId,
                            Amounts,
                            Status,
                            InvestTime,
                            ProjectName,
                            TotalLoan,
                            ProjectDuration,
                            DurationMonthOrDay,
                            MinTenderAmount,
                            AnnualisedRates,
                            AddAnnualisedRates,
                            CreateTime,
                            LoanTime";

            string sqlWhere = " and MemberId=" + id;

            if (!string.IsNullOrEmpty(condition.day))
            {
                sqlWhere += " and DATEDIFF(DD,InvestTime,GETDATE())<=" + condition.day;
            }
            if (!string.IsNullOrEmpty(condition.type))
            {
                sqlWhere += " and Status>0 and RepaymentStatus=" + condition.type;
            }
            if (!string.IsNullOrEmpty(condition.status))
            {
                sqlWhere += " and Status=" + condition.status;
            }

            return base.PageGet<T_Project_EX>(view, cols, @"View_GetProducts2", sqlWhere, "[InvestTime] DESC", "");

        }

        //删除我的投资
        public int DeleteMyInvest(string id)
        {
            string sql = "delete from T_Investment where SubmitBidNo=@SubmitBidNo";
            return base.Excute(sql, new { SubmitBidNo = id });
        }

        //获取项目证件
        public List<T_Document_EX> GetDocImage(string pid)
        {
            string sql = @"select DocPath,[Type],
                            case when [Type]=1 then '相关文件'
                            when [Type]=2 then '实地照片'
                            when [Type]=3 then '货后跟踪'
                            when [Type]=4 then '保证合同'
                            when [Type]=5 then '承租人证件'
                            when [Type]=6 then '他证'
                            end TypeName,
                         ROW_NUMBER() over(partition by [Type] order by Id ASC) Num 
                         from T_Document where ProjectId=@ProjectId";

            return Query<T_Document_EX>(sql, new { ProjectId = pid });
        }

        //统计投资金额
        public decimal GetAmountsByPid(string pid)
        {
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(pid))
            {
                sqlWhere = "and ProjectId=@ProjectId";
            }

            string sql = "select SUM(TenderAmt) as amount from T_Investment where Status>0 " + sqlWhere;

            return base.Get<T_Investment_EX>(sql, new { ProjectId = pid }).amount;
        }

        //统计投标收益
        public decimal GetInvestProfits(string mid)
        {
            string sqlWhere = string.Empty;
            if (!string.IsNullOrEmpty(mid))
            {
                sqlWhere = "and MemberId=@MemberId";
            }
            string sql = "select SUM(InCome) as InCome from U_FundsFlow where 1=1 " + sqlWhere;
            return base.Get<U_FundsFlow>(sql, new { MemberId = mid }).InCome;
        }

        //获取项目投资记录
        public PagedList<T_Investment_EX> GetInvestors(PageView view, string pid)
        {
            string cols = "ID,Name,MemberId,TenderAmt,CreateTime,[Status]";

            string sqlWhere = " and ID=" + pid;

            return base.PageGet<T_Investment_EX>(view, cols, @"View_GetInvestors ", sqlWhere, "[ID] ASC", "");
        }

        //修改项目状态
        public int UpdateProject(string projectId, DateTime time, int status)
        {
            string sql = "update T_Project set TenderStatus=@TenderStatus,BiddingDeadline=@BiddingDeadline where ID=@ID";
            return base.Excute(sql, new { TenderStatus = status, BiddingDeadline = time, ID = projectId });
        }

        //根据投资编号获取合同信息
        public T_Contract GetContractByNumber(string number)
        {
            string sql = @"SELECT [ID]
                          ,[ProjectId]
                          ,[ProjectOrderId]
                          ,[Ordernumber]
                          ,[Path]
                          ,[Status]
                      FROM [T_Contract]
                      WHERE Ordernumber=@Ordernumber ";
            return base.Get<T_Contract>(sql, new { Ordernumber = number });

        }

        //是否新手项目
        public bool IsGreenHand(string mid)
        {
            string sql = @"select
                            T_Investment.ProjectId 
                            from T_Investment,T_Project 
                            WHERE T_Investment.ProjectId=T_Project.ID 
                            and T_Project.IsNewProject=1 and MemberId=@MemberId";
            return base.Query<T_Investment>(sql, new { MemberId = mid }).Count <= 0 ? true : false;
        }
    }
}
